package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dao.ProductTypeDAO;
import com.model.ProductType;
import com.util.DB;

public class ProductTypeDAOImpl implements ProductTypeDAO {
	// private static Connection conn = DB.getConnection();
	private Connection conn = DB.con;

	@Override
	public boolean addProductType(ProductType productType) {
		// TODO Auto-generated method stub
		PreparedStatement st = null;
		try {
			st = conn
					.prepareStatement("insert into producttype(ID,PRODUCTTYPENAME,DESCRIPTION) values(?,?,?)");
			st.setInt(1, productType.getId());
			st.setString(2, productType.getProductTypeName());
			st.setString(3, productType.getDescription());
			int num = st.executeUpdate();
			if (num > 0) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			DB.close(st, null);
		}
		return false;
	}

	@Override
	public boolean deleteProductType(int id) {
		// TODO Auto-generated method stub
		PreparedStatement st = null;
		try {
			st = conn.prepareStatement("delete from producttype where id =?");
			st.setInt(1, id);
			int num = st.executeUpdate();
			if (num > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return false;
	}

	@Override
	public boolean updateProductType(ProductType productType, int id) {
		// TODO Auto-generated method stub
		PreparedStatement st = null;
		try {
			st = conn
					.prepareStatement("update productType set ID = ?, PRODUCTTYPENAME = ?, DESCRIPTION = ? where ID = ?");
			st.setInt(1, productType.getId());
			st.setString(2, productType.getProductTypeName());
			st.setString(3, productType.getDescription());
			st.setInt(4, id);
			int num = st.executeUpdate();
			if (num > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return false;
	}

	@Override
	public List<ProductType> selectProductType(String key, String type,
			int page, int rows, String sort, String order) {
		// TODO Auto-generated method stub
		List<ProductType> list = new ArrayList<ProductType>();
		PreparedStatement st = null;
		int startRows = (page - 1) * rows;
		int endRows = rows;
		try {
			if (key != null) {
				String sql = "select * from productType where productTypeName like ? order by ID ASC limit ?,?";
				sql = sql.replace("productTypeName", type);
				sql = sql.replace("ID", sort);
				sql = sql.replace("ASC", order);
				st = conn.prepareStatement(sql);
				st.setString(1, "%" + key + "%");
				st.setInt(2, startRows);
				st.setInt(3, endRows);
				ResultSet rs = st.executeQuery();
				// 循环取出数据
				while (rs.next()) {
					ProductType productType = new ProductType();
					productType.setId(rs.getInt(1));
					productType.setProductTypeName(rs.getString(2));
					productType.setDescription(rs.getString(3));
					list.add(productType);
				}
				return list;
			} else {
				String sql = "select * from productType order by id ASC limit ?,?";
				sql = sql.replace("id", sort);
				sql = sql.replace("ASC", order);
				st = conn.prepareStatement(sql);
				st.setInt(1, startRows);
				st.setInt(2, endRows);
				ResultSet rs = st.executeQuery();
				// 循环取出数据
				while (rs.next()) {
					ProductType productType = new ProductType();
					productType.setId(rs.getInt(1));
					productType.setProductTypeName(rs.getString(2));
					productType.setDescription(rs.getString(3));
					list.add(productType);
				}
				return list;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return null;
	}

	// 查询图书类别
	public List<ProductType> selectProductType2() {
		List<ProductType> list = new ArrayList<ProductType>();
		PreparedStatement st = null;
		try {
			String sql = "select * from productType";
			st = conn.prepareStatement(sql);
			ResultSet rs = st.executeQuery();
			// 循环取出数据
			while (rs.next()) {
				ProductType productType = new ProductType();
				productType.setId(rs.getInt(1));
				productType.setProductTypeName(rs.getString(2));
				productType.setDescription(rs.getString(3));
				list.add(productType);
			}
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return null;

	}

	public int getTotal() {
		PreparedStatement st = null;
		int count = 0;
		try {
			st = conn.prepareStatement("select * from producttype");
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				count++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return count;
	}

	public int getLikeTotal(String key, String type) {
		PreparedStatement st = null;
		int count = 0;
		try {
			String sql = "select * from producttype where id like ?";
			sql = sql.replace("id", type);
			st = conn.prepareStatement(sql);
			st.setString(1, "%" + key + "%");
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				count++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return count;
	}

}
